package com.dullong.firedocmanager.service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import com.dullong.firedocmanager.bean.Cate;
import com.dullong.firedocmanager.service.base.BaseServiceSupport;
import com.dullong.firedocmanager.util.ConnectionUtil;

@Service
public class CateServiceSupport implements CateService {

	@Override
	public Integer save(String title, Integer parentid) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.update(conn,
					"insert into cate(title,parent) values(?,?)", title,
					parentid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return 0;

	}

	@Override
	public Integer update(String title, Integer parentid, Integer id) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.update(conn,
					"update cate set title=?,parent=? where id=?", title,
					parentid, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return 0;
	}

	@Override
	public Integer delete(Integer id) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.update(conn, "delete from cate where id=?", id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return 0;

	}

	@Override
	public Cate get(Integer id) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			Cate cate = runner.query(conn,
					"select id,title,parent from cate where id=?",
					new BeanHandler<Cate>(Cate.class), id);
			return cate;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

	@Override
	public List<Cate> listByParent(Integer parent) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<Cate> list2 = runner.query(conn,
					"select id,title,parent from cate where parent=?",
					new BeanListHandler<Cate>(Cate.class), parent);
			return list2;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

	@Override
	public List<Cate> listRootCate() {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<Cate> list2 = runner.query(conn,
					"select id,title,parent from cate where parent is null",
					new BeanListHandler<Cate>(Cate.class));
			return list2;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

	@Override
	public List<Cate> listAll() {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<Cate> list2 = runner.query(conn,
					"select id,title,parent from cate",
					new BeanListHandler<Cate>(Cate.class));
			return list2;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

}
